﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace SMS.dao
{
    public class StudentDao
    {
        private static readonly StudentDao INSTANCE = new StudentDao();

        public static StudentDao Instance
        {
            get
            {
                return INSTANCE;
            }
        }

        private StudentDao() { }

        public int insert(String fullName, DateTime dob, String email, bool gender, String address)
        {
            String sql = "insert into student (stu_fullname, stu_dob, stu_email, stu_gender, stu_address) values " +
                         "(@fullName, @dob, @email, @gender, @address)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@fullName", fullName));
            DataFetcher.Instance.addParameter(new SqlParameter("@dob", dob));
            DataFetcher.Instance.addParameter(new SqlParameter("@email", email));
            DataFetcher.Instance.addParameter(new SqlParameter("@gender", gender));
            DataFetcher.Instance.addParameter(new SqlParameter("@address", address));
            return DataFetcher.Instance.execute();
        }

        public DataTable findStudentsNotIn(int clazzId)
        {
            String sql = "select stu_id, stu_fullname " +
                         "from student " +
                         "where stu_id not in (select s.stu_id " +
                                              "from STUDENT s, REGISTRATION r " +
                                              "where s.STU_ID = r.STU_ID and r.CLA_ID = @clazzId)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzId", clazzId));
            return DataFetcher.Instance.fetch();
        }

        public DataTable findStudentsIn(int clazzId)
        {
            String sql = "select stu_id, stu_fullname " +
                         "from student " +
                         "where stu_id in (select s.stu_id " +
                                              "from STUDENT s, REGISTRATION r " +
                                              "where s.STU_ID = r.STU_ID and r.CLA_ID = @clazzId)";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzId", clazzId));
            return DataFetcher.Instance.fetch();
        }

        public DataTable findAll()
        {
            String sql = "select stu_id, stu_fullname, convert(varchar, stu_dob, 103) as stu_dob, stu_email, case stu_gender when 1 then 'Male' else 'Female' end as gender, stu_address " +
                         "from student " +
                         "order by stu_fullname";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public ArrayList findById(int stuId)
        {
            String sql = "select stu_fullname, stu_dob, stu_email, stu_gender, stu_address " +
                         "from student " +
                         "where stu_id = @stuId";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@stuId", stuId));
            return DataFetcher.Instance.getOneRow();
        }

		public DataTable findStudents(String clazzId)
        {
            String sql = "select s.stu_id, s.stu_fullname, CONVERT(VARCHAR(10), s.STU_DOB, 103) AS dob, s.stu_email, case s.stu_gender when 1 then 'Male' else 'Female' end as gender, s.stu_address " +
                         "from STUDENT s, REGISTRATION r " +
                         "where s.STU_ID = r.STU_ID and r.CLA_ID = @clazzId";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@clazzId", clazzId));
            return DataFetcher.Instance.fetch();
        }

        public int update(int stuId, String fullName, 
                                 DateTime dob, String email, bool gender, String address)
        {
            String sql = "update student set stu_fullname = @fullName, stu_dob = @dob, " +
                            "stu_email = @email, stu_gender = @gender, stu_address = @address where stu_id = @stuId";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@stuId", stuId));
            DataFetcher.Instance.addParameter(new SqlParameter("@fullName", fullName));
            DataFetcher.Instance.addParameter(new SqlParameter("@dob", dob));
            DataFetcher.Instance.addParameter(new SqlParameter("@email", email));
            DataFetcher.Instance.addParameter(new SqlParameter("@gender", gender));
            DataFetcher.Instance.addParameter(new SqlParameter("@address", address));
            return DataFetcher.Instance.execute();
        }

        public DataTable findBy(String fullName)
        {
            String sql = "select stu_id, stu_fullname, convert(varchar, stu_dob, 101) as stu_dob, stu_email, case stu_gender when 1 then 'Male' else 'Female' end as gender, stu_address " +
                         "from student " +
                         "where stu_fullname like N'%" + fullName + "%' " + 
                         "order by stu_fullname";
            DataFetcher.Instance.setCommand(sql, 1);
            return DataFetcher.Instance.fetch();
        }

        public DataTable findBy(int classId, int courseId, int semesterId)
        {
            //String sql = "select tmp.STU_FULLNAME, tmp.STU_EMAIL, g.GRA_FIFTEENMINTEST, g.GRA_SIXTYMINTEST, g.GRA_FINALTEST, tmp.stu_id, g.gra_id " +
            //             "from grade g, course c, semester s, " +
            //                  "(select stu.STU_ID, c.CLA_ID, stu.STU_FULLNAME, stu.STU_EMAIL " +
            //                  "from student stu, clazz c, registration r " +
            //                  "where stu.STU_ID = r.STU_ID and c.CLA_ID = r.CLA_ID) as tmp " +
            //             "where g.COR_ID = c.COR_ID and g.SEM_ID = s.SEM_ID and g.STU_ID = tmp.STU_ID and g.CLA_ID = tmp.CLA_ID and " +
            //                   "s.SEM_ID = @semId and tmp.CLA_ID = @claId and c.COR_ID = @corId";
            String sql = "select stu.STU_ID, stu.STU_FULLNAME, stu.STU_EMAIL, " +
                                 "(select GRA_FIFTEENMINTEST " +
                                 "from GRADE g " +
                                 "where g.STU_ID = stu.STU_ID and g.CLA_ID = c.CLA_ID and g.SEM_ID = @semId and g.COR_ID = @corId) as fifteen, " +
                                 "(select GRA_SIXTYMINTEST " +
                                 "from GRADE g " +
                                 "where g.STU_ID = stu.STU_ID and g.CLA_ID = c.CLA_ID and g.SEM_ID = @semId and g.COR_ID = @corId) as sixty, " +
                                 "(select GRA_FINALTEST " +
                                 "from GRADE g " +
                                 "where g.STU_ID = stu.STU_ID and g.CLA_ID = c.CLA_ID and g.SEM_ID = @semId and g.COR_ID = @corId) as final, " +
                                 "(select g.GRA_ID " +
                                 "from GRADE g " +
                                 "where g.STU_ID = stu.STU_ID and g.CLA_ID = c.CLA_ID and g.SEM_ID = @semId and g.COR_ID = @corId) as graId " +
                          "from student stu, clazz c, registration r " +
                          "where stu.STU_ID = r.STU_ID and c.CLA_ID = r.CLA_ID and c.cla_id = @claId";
            DataFetcher.Instance.setCommand(sql, 1);
            DataFetcher.Instance.addParameter(new SqlParameter("@semId", semesterId));
            DataFetcher.Instance.addParameter(new SqlParameter("@claId", classId));
            DataFetcher.Instance.addParameter(new SqlParameter("@corId", courseId));
            return DataFetcher.Instance.fetch();
        }
    }
}
